using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using PropertyExpression.Common;

namespace PropertyExpression.ControllableQuery.Toolkit
{
    public interface IQRepository
    {
        void Add(SqlCommand sqlCommand, QLocation qLocation);
    }

    public class DoNothingQRepository : IQRepository
    {
        public void Add(SqlCommand sqlCommand, QLocation qLocation)
        {
            //no-op
        }
    }

    public class FindUsagesQRepository : IQRepository
    {
        private readonly Dictionary<MethodInfo, HashSet<string>> queries = new Dictionary<MethodInfo, HashSet<string>>();

        public void Add(SqlCommand sqlCommand, QLocation qLocation)
        {
            var builder = new StringBuilder();
            foreach (SqlParameter sqlParameter in sqlCommand.Parameters)
            {
                if (builder.Length > 0) builder.AppendLine(",");
                builder.Append(sqlParameter.ParameterName + " " + GetSqlTypeString(sqlParameter));
            }
            var methodInfo = qLocation.Match(
                @class => { throw new NotImplementedException(); },
                method => method.MethodInfo,
                nonDisplay => nonDisplay.MethodInfo);
            HashSet<string> hashSet;
            if (!queries.TryGetValue(methodInfo, out hashSet))
            {
                hashSet = new HashSet<string>();
                queries.Add(methodInfo, hashSet);
            }
            if (builder.Length != 0) builder.AppendLine();
            hashSet.Add(string.Format(@"{0}AS 
    BEGIN
{1}
    END",
                                      builder,
                                      sqlCommand.CommandText));
        }

        public IEnumerable<MethodInfo> FindUsages(string connectionString, string referencedEntityName, IOption<string> referencedMinorName)
        {
            var procedureNamePrefix = Guid.NewGuid().ToString("N");
            return connectionString.WithSqlConnection(
                connection =>
                from query in queries
                where query.Value.Any(
                    procedureBody => {
                        DropProcedure(connection, procedureNamePrefix);
                        connection.WithSqlCommand(
                            command => {
                                command.CommandText = string.Format(@"CREATE PROCEDURE ControllableQueryTemp{0}
{1}",
                                                                    procedureNamePrefix,
                                                                    procedureBody);
                                command.ExecuteNonQuery();
                                return new {};
                            });
                        var result = connection.WithSqlCommand(
                            command => {
                                var builder = new StringBuilder();
                                builder.AppendFormat(@"SELECT  1
FROM    sys.dm_sql_referenced_entities('dbo.ControllableQueryTemp{0}', 'OBJECT')
WHERE   referenced_entity_name = @referenced_entity_name", procedureNamePrefix);
                                if (referencedMinorName.HasValue)
                                {
                                    builder.Append(@"
        AND referenced_minor_name = @referenced_minor_name");
                                    command.Parameters.Add(new SqlParameter {
                                                                                SqlDbType = SqlDbType.NVarChar,
                                                                                Size = -1,
                                                                                ParameterName = "@referenced_minor_name",
                                                                                Value = referencedMinorName.Value
                                                                            });
                                }
                                else builder.Append(@"
        AND referenced_minor_name IS NULL");
                                command.Parameters.Add(new SqlParameter {
                                                                            SqlDbType = SqlDbType.NVarChar,
                                                                            Size = -1,
                                                                            ParameterName = "@referenced_entity_name",
                                                                            Value = referencedEntityName
                                                                        });
                                command.CommandText = builder.ToString();
                                return command.ExecuteScalar();
                            });
                        DropProcedure(connection, procedureNamePrefix);
                        return result != null;
                    })
                select query.Key);
        }

        private static void DropProcedure(SqlConnection connection, string procedureNamePrefix)
        {
            connection.WithSqlCommand(
                command => {
                    command.CommandText = string.Format(@"IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[ControllableQueryTemp{0}]')
                    AND type IN ( N'P', N'PC' ) ) 
    DROP PROCEDURE [dbo].[ControllableQueryTemp{0}]", procedureNamePrefix);
                    command.ExecuteNonQuery();
                    return new {};
                });
        }

        private static string GetSqlTypeString(SqlParameter sqlParameter)
        {
            switch (sqlParameter.SqlDbType)
            {
                case SqlDbType.UniqueIdentifier:
                    return "UNIQUEIDENTIFIER";
                case SqlDbType.Int:
                    return "INT";
                case SqlDbType.NVarChar:
                    if (sqlParameter.Size == -1)
                        return "NVARCHAR(MAX)";
                    else
                        throw new ApplicationException();
                default:
                    throw new ApplicationException();
            }
        }
    }
}